package com.person.jc.service.security;

import java.util.List;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class TsMenuService {
	
	public static final TsMenuService me = new TsMenuService();
	
	public List<Record> getMenuByUserId(String userId){
		StringBuffer sql = new StringBuffer(
    			"select a.*\n" +
    			"  from (select t.*\n" + 
    			"          from ts_sys_menu t, ts_user_role_vcl t1, ts_role_menu_vcl t2, ts_role t3\n" + 
    			"         where t.menu_id = t2.menu_id\n" + 
    			"           and t2.role_id = t1.role_id\n" + 
    			"           and t1.role_id = t3.role_id\n" + 
    			"           and t3.status = 1\n" + 
    			"			and t.menu_lvl <> 4 "+
    			"			and t.if_valid = 1"+
    			"           and t1.user_id = ?\n" + 
    			"        union\n" + 
    			"        select t.*\n" + 
    			"          from ts_sys_menu t, ts_user_menu_vcl t1\n" + 
    			"         where t.menu_id = t1.menu_id\n" + 
    			"           and t1.user_id = ?\n" + 
    			"			and t.menu_lvl <> 4 "+
    			"			and t.if_valid = 1 ) a\n" + 
    			"  order by  menu_lvl,idx_no,menu_id");
		return Db.find(sql.toString(),userId,userId);
	}
	
	public List<Record> getFunByUserId(String userId){
		StringBuffer sql = new StringBuffer(
    			"select a.*\n" +
    			"  from (select t.*\n" + 
    			"          from ts_sys_menu t, ts_user_role_vcl t1, ts_role_menu_vcl t2, ts_role t3\n" + 
    			"         where t.menu_id = t2.menu_id\n" + 
    			"           and t2.role_id = t1.role_id\n" + 
    			"           and t1.role_id = t3.role_id\n" + 
    			"           and t3.status = 1\n" + 
    			"			and t.menu_lvl = 4 "+
    			"			and t.if_valid = 1"+
    			"           and t1.user_id = ?\n" + 
    			"        union\n" + 
    			"        select t.*\n" + 
    			"          from ts_sys_menu t, ts_user_menu_vcl t1\n" + 
    			"         where t.menu_id = t1.menu_id\n" + 
    			"           and t1.user_id = ?\n" + 
    			"			and t.if_valid = 1"+
    			"			and t.menu_lvl = 4 ) a\n" + 
    			"  order by  menu_lvl,idx_no,menu_id");
		return Db.find(sql.toString(),userId,userId);
	}

	public List<Record> getAllMenuByUserId(String userId) {
		StringBuffer sql = new StringBuffer(
    			"select a.*\n" +
    			"  from (select t.*\n" + 
    			"          from ts_sys_menu t, ts_user_role_vcl t1, ts_role_menu_vcl t2, ts_role t3\n" + 
    			"         where t.menu_id = t2.menu_id\n" + 
    			"           and t2.role_id = t1.role_id\n" + 
    			"           and t1.role_id = t3.role_id\n" + 
    			"           and t3.status = 1\n" + 
    			"			and t.if_valid = 1"+
    			"           and t1.user_id = ?\n" + 
    			"        union\n" + 
    			"        select t.*\n" + 
    			"          from ts_sys_menu t, ts_user_menu_vcl t1\n" + 
    			"         where t.menu_id = t1.menu_id\n" + 
    			"           and t1.user_id = ?\n" + 
    			"			and t.if_valid = 1) a\n" + 
    			"  order by  menu_lvl,idx_no,menu_id");
		return Db.find(sql.toString(),userId,userId);
	}
}
